-- for example, on all a server there's only one Berreta M9 model
-- each user could have a differrent Berreta M9 though
-- which is allowed by defining global patter in ITEMS table
-- and specific ones in GUNFRAMES table
CREATE TABLE ITEMS (
  id_items        SERIAL       PRIMARY KEY UNIQUE,

  fk_material     INT          REFERENCES MATERIALS(id_materials),
  fk_itemtypes    INT          REFERENCES ITEMTYPES(id_itemtypes),
  fk_brand        INT          REFERENCES BRANDS(id_brands),

  name            VARCHAR(32)  NOT NULL,
  weight          FLOAT        DEFAULT 0, -- in grams
  length          INT          DEFAULT 0, -- in mm

  waterproof      BOOLEAN      DEFAULT TRUE,
  --  pickable        BOOLEAN      DEFAULT TRUE, -- allow to pick it up
  --  bulletproof     BOOLEAN      DEFAULT FALSE, -- rly need to put that here ?
  -- float _height;
  -- float _depth;
  -- Vector _position;
  -- Vector _orientation;

  -- 0: unable to sell it
  -- any other positive value: price
  -- unit ? Dollar / fictive ?
  price           MONEY        DEFAULT 0, -- base-price; Code: altered by durability

  description     VARCHAR(32)  DEFAULT 'unknown'-- audio file name
);


-- fixations: required_barrel_length field || length (=> cumulable)

-- always on top of the barrel ?
CREATE TABLE SCOPES (
  id_scopes        INT          NOT NULL PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),

  required_length  FLOAT        NOT NULL
);


CREATE TABLE STOCKS (
  id_stocks        INT          NOT NULL PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),

  absorption_coef  FLOAT        DEFAULT 0,
  readiness_time   FLOAT        DEFAULT 0,

  -- how much does it slow your character rotation down
  -- length proportionnal ? / usefull ?
  -- [need to move the whole arm (but only it), whatever circonstances]
  rotation_coeff   FLOAT        DEFAULT 0,
  cylinder         BOOLEAN      DEFAULT FALSE
);


CREATE TABLE ACCESSORIES (
  id_accessories   INT          NOT NULL PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),

  -- required space on barrel
  required_length  FLOAT        NOT NULL,

  -- values matches code enum which are:
  -- 0: bottom
  -- 1: left side
  -- 2: right side
  position         SMALLINT     DEFAULT 0
);


CREATE TABLE POWERSOURCES (
  id_powersources  INT          NOT NULL PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),

  duration         FLOAT        NOT NULL, -- in hours
  status           FLOAT        DEFAULT 100, -- percentage

  -- -1: unlimited battery (auto-reloading (kinetic / solar))
  -- 0: unable to reload it (cells) [=> extractible]
  -- 1: non extractible battery
  -- 2: extractible battery
  format           SMALLINT     DEFAULT 0
);


CREATE TABLE FLASHLIGHTS (
  id_flashlights   INT          NOT NULL PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),
  fk_powersource   INT          REFERENCES POWERSOURCES(id_powersources),

  luminosity       INT          NOT NULL -- candella
  -- pattern_(file)name varchar

  -- stock last event (on / off) timestamp [on character deco, update @off]
);


CREATE TABLE BARRELS (
  id_barrels       SERIAL       UNIQUE PRIMARY KEY,
  fk_item          INT          REFERENCES ITEMS(id_items),
  caliber          FLOAT        NOT NULL -- tmp: in mm if > 1, else: imperial inches --

  -- CONSTRAINT barrel_unicity UNIQUE (name, caliber, length)
);


CREATE TABLE CLIPS (
  id_clips         SERIAL       PRIMARY KEY UNIQUE,
  fk_item          INT          REFERENCES ITEMS(id_items),

  capacity         INT          NULL, -- == 0 => infinity (tazer, ...)
  caliber          FLOAT        NOT NULL
);

\i guns.sql
